﻿using System.Data;
using Common.Constant;
using Entity;
using log4net;
using System.Data.SqlClient;
using Blog.Common.Helpers;

namespace DataAccess
{
    public class PostDao
    {
        private static readonly ILog Logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private SqlConnection _sqlCon;
        private DataSet _dataSet;
        public DataSet GetPostById(int postId)
        {
            try
            {
                var sqlParameters = new SqlParameter[1];
                sqlParameters[0] = new SqlParameter("@PostId", SqlDbType.Int) { Value = postId };
                _sqlCon = DBConnection.SqlGetConnection();
                _dataSet = SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetPostById,sqlParameters);
                return _dataSet;
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public DataSet GetNewPost(int numberOfPost)
        {
            try
            {
                var sqlParameters = new SqlParameter[1];
                sqlParameters[0] = new SqlParameter("@NumberOfPost", SqlDbType.Int) {Value = numberOfPost};
                _sqlCon = DBConnection.SqlGetConnection();
                _dataSet = SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetNewPost,sqlParameters);
                return _dataSet;
                
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public DataSet SelectAllPost()
        {
            try
            {
                _sqlCon = DBConnection.SqlGetConnection();
                return SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetAllPost);
                 

            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }
        public DataSet GetPostByCategoryId(int categoryId)
        {
            try
            {
                var sqlParameters = new SqlParameter[1];
                sqlParameters[0] = new SqlParameter("@CategoryId",DbType.Int32){Value = categoryId};
                _sqlCon = DBConnection.SqlGetConnection();
                _dataSet = SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetPostByCategoryId,sqlParameters);
                return _dataSet;

            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public DataSet SelectTopPost()
        {
            try
            {
                _sqlCon = DBConnection.SqlGetConnection();
                _dataSet = SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetTopPost);
                return _dataSet;

            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public DataSet GetPostByUserName(string userName)
        {
            try
            {
                var sqlParametters = new SqlParameter[1];
                sqlParametters[0] = new SqlParameter("@UserName",DbType.String){Value = userName};
                _sqlCon = DBConnection.SqlGetConnection();
                _dataSet = SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetPostByUser,sqlParametters);
                return _dataSet;

            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public DataSet GetPostByUserForManager(string userName)
        {
            try
            {
                var sqlParametters = new SqlParameter[1];
                sqlParametters[0] = new SqlParameter("@UserName", DbType.String) { Value = userName };
                _sqlCon = DBConnection.SqlGetConnection();
                _dataSet = SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetPostByUserForManager, sqlParametters);
                return _dataSet;

            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public DataSet GetPostByDate(string date)
        {
            try
            {
                var sqlParametters = new SqlParameter[1];
                sqlParametters[0] = new SqlParameter("@Postdate", DbType.String) { Value = date };
                _sqlCon = DBConnection.SqlGetConnection();
                _dataSet = SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetPostByDate, sqlParametters);
                return _dataSet;

            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public DataSet GetTopViewPost(int numberOfPost)
        {
            try
            {
                var sqlParametters = new SqlParameter[1];
                sqlParametters[0] = new SqlParameter("@NumberOfPost", DbType.String) { Value = numberOfPost };
                _sqlCon = DBConnection.SqlGetConnection();
                _dataSet = SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetTopViewPost, sqlParametters);
                return _dataSet;

            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public DataSet GetTopCommentPost(int numberOfPost)
        {
            try
            {
                var sqlParametters = new SqlParameter[1];
                sqlParametters[0] = new SqlParameter("@NumberOfPost", DbType.String) { Value = numberOfPost };
                _sqlCon = DBConnection.SqlGetConnection();
                _dataSet = SqlHelper.ExecuteDataSet(_sqlCon, CommandType.StoredProcedure, DBSchemas.GetTopCommentPost, sqlParametters);
                return _dataSet;

            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public int AddPost(Post post)
        {
            try
            {
                var sqlParametters = new SqlParameter[10];
                sqlParametters[0] = new SqlParameter("@CategoryId",DbType.Int32){Value = post.CategoryId};
                sqlParametters[1] = new SqlParameter("@PostName", DbType.String) { Value = post.PostName };
                sqlParametters[2] = new SqlParameter("@PostDescription", DbType.String) { Value = post.PostDescription };
                sqlParametters[3] = new SqlParameter("@PostContent", DbType.String) { Value = post.PostContent };
                sqlParametters[4] = new SqlParameter("@Postdate", DbType.DateTime) { Value = post.Postdate };
                sqlParametters[5] = new SqlParameter("@PicturePath", DbType.String) { Value = post.PicturePath };
                sqlParametters[6] = new SqlParameter("@UserName", DbType.String) { Value = post.UserName };
                sqlParametters[7] = new SqlParameter("@ViewCount", DbType.Int32) { Value = post.ViewCount };
                sqlParametters[8] = new SqlParameter("@CommentCount", DbType.Int32) { Value = post.CommentCount };
                sqlParametters[9] = new SqlParameter("@IsPublish", DbType.Boolean) { Value = post.IsPublish };
                _sqlCon = DBConnection.SqlGetConnection();
                return SqlHelper.ExecuteNonQuery(_sqlCon, CommandType.StoredProcedure, DBSchemas.AddPost, sqlParametters);

            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }
        public int UpdatePost(Post post)
        {
            try
            {
                var sqlParametters = new SqlParameter[10];
                sqlParametters[0] = new SqlParameter("@CategoryId", DbType.Int32) { Value = post.CategoryId };
                sqlParametters[1] = new SqlParameter("@PostName", DbType.String) { Value = post.PostName };
                sqlParametters[2] = new SqlParameter("@PostDescription", DbType.String) { Value = post.PostDescription };
                sqlParametters[3] = new SqlParameter("@PostContent", DbType.String) { Value = post.PostContent };
                sqlParametters[4] = new SqlParameter("@Postdate", DbType.DateTime) { Value = post.Postdate };
                sqlParametters[5] = new SqlParameter("@PicturePath", DbType.String) { Value = post.PicturePath };
                sqlParametters[6] = new SqlParameter("@UserName", DbType.String) { Value = post.UserName };
                sqlParametters[7] = new SqlParameter("@ViewCount", DbType.Int32) { Value = post.ViewCount };
                sqlParametters[8] = new SqlParameter("@CommentCount", DbType.Int32) { Value = post.CommentCount };
                sqlParametters[9] = new SqlParameter("@PostId", DbType.Int32) { Value = post.PostId };
                _sqlCon = DBConnection.SqlGetConnection();
                return SqlHelper.ExecuteNonQuery(_sqlCon, CommandType.StoredProcedure, DBSchemas.UpdatePost, sqlParametters);

            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }
        public int DeletePost(int postId)
        {
            try
            {
                var sqlParametters = new SqlParameter[1];
                sqlParametters[0] = new SqlParameter("@PostId", DbType.String) { Value = postId };
                _sqlCon = DBConnection.SqlGetConnection();
                return SqlHelper.ExecuteNonQuery(_sqlCon, CommandType.StoredProcedure, DBSchemas.DeletePost, sqlParametters);
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public int UpdatePostView(int postId)
        {
            try
            {
                var sqlParametters = new SqlParameter[1];
                sqlParametters[0] = new SqlParameter("@PostId", DbType.String) { Value = postId };
                _sqlCon = DBConnection.SqlGetConnection();
                return SqlHelper.ExecuteNonQuery(_sqlCon, CommandType.StoredProcedure, DBSchemas.UpdatePostView, sqlParametters);
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }
        public int UpdateCommentCount(int postId)
        {
            try
            {
                var sqlParametters = new SqlParameter[1];
                sqlParametters[0] = new SqlParameter("@PostId", DbType.String) { Value = postId };
                _sqlCon = DBConnection.SqlGetConnection();
                return SqlHelper.ExecuteNonQuery(_sqlCon, CommandType.StoredProcedure, DBSchemas.UpdatePostComment, sqlParametters);
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

        public int PublishPost(int postId,bool isPublish)
        {
            try
            {
                var sqlParametters = new SqlParameter[2];
                sqlParametters[0] = new SqlParameter("@PostId", DbType.String) { Value = postId };
                sqlParametters[1] = new SqlParameter("@IsPublish", DbType.Boolean) { Value = isPublish };
                _sqlCon = DBConnection.SqlGetConnection();
                return SqlHelper.ExecuteNonQuery(_sqlCon, CommandType.StoredProcedure, DBSchemas.PublishPost, sqlParametters);
            }
            catch (SqlException sqlException)
            {
                Logger.Error(sqlException.Message);
                throw;
            }
        }

    }
}
